<?php
/**
 * Created by PhpStorm.
 *
 * @author Tian.Yuxing
 * @email tianyuxing@sciall.org.cn
 * Date: 2025/4/23
 * Time: 10:05
 */

namespace Modules\DataCheck\Excel\Tratis;

use Modules\DataCheck\Enums\ErrorEnum;
use Modules\DataCheck\Exceptions\DataCheckException;
use Vtiful\Kernel\Format;

/**
 * Excel处理工具特性
 * 
 * 提供Excel数据处理的通用方法
 * 可被多个服务类复用，实现Excel数据的转换和处理
 */
trait Excel
{
    protected $border;

    /**
     * 数据翻译转换
     * 
     * 将Excel原始数据按照字段映射转换为系统数据结构
     * 支持字段映射、数据补充和强制赋值等功能
     * 
     * @author Tian.Yuxing
     * @param array $fieldMap 字段映射配置，格式为[列编号=>字段名]
     * @param array $sourceData Excel原始数据数组
     * @param array $appendData 需要补充的数据，格式为[字段名=>值]
     * @param bool $forceAssign 是否强制赋值，为true时忽略源数据列不存在的错误
     * @param int $line 当前行数，用于抛出异常
     * @param string $sheetName Excel工作表名称
     * 
     * @return array 转换后的数据数组
     * @throws DataCheckException 当源数据列不存在且非强制赋值时抛出异常
     */
    public function transDataT(array $fieldMap, array $sourceData, array $appendData, bool $forceAssign = false, int $line = 0, string $sheetName = ''): array
    {
        // 初始化数据容器
        $xmlData = [];

        // 处理字段映射
        foreach ($fieldMap as $column => $field) {
            // 跳过空字段
            if (empty($field)) {
                continue;
            }

            $key = $column - 1;
            // 检查数据源是否存在该列
            if (!$forceAssign && !isset($sourceData[$key])) {
                $sheetName = $sheetName ? "【{$sheetName}】" : '';
                $msg = "Excel文件{$sheetName}第 {$column} 列数据不存在";
                if ($line > 0) {
                    $msg = "Excel文件{$sheetName}第 {$line} 行第 {$column} 列数据不存在";
                }
                throw new DataCheckException($msg, ErrorEnum::EXCEL);
            }
            //获取数据值
            $xmlData[$field] = isset($sourceData[$key]) ? trim($sourceData[$key]) : '';
        }

        // 补充数据处理
        if (empty($appendData)) {
            return $xmlData;
        }

        foreach ($appendData as $fieldCode => $val) {
            if (empty($xmlData[$fieldCode])) {
                $xmlData[$fieldCode] = $val;
            }
        }
        return $xmlData;
    }

    /**
     * 取静态列名(查表法)
     * @param $count
     * @return array
     */
    private static function staticColumn($count): array
    {
        $columns = [
            0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F', 6 => 'G', 7 => 'H', 8 => 'I', 9 => 'J',
            10 => 'K', 11 => 'L', 12 => 'M', 13 => 'N', 14 => 'O', 15 => 'P', 16 => 'Q', 17 => 'R', 18 => 'S', 19 => 'T',
            20 => 'U', 21 => 'V', 22 => 'W', 23 => 'X', 24 => 'Y', 25 => 'Z', 26 => 'AA', 27 => 'AB', 28 => 'AC', 29 => 'AD',
            30 => 'AE', 31 => 'AF', 32 => 'AG', 33 => 'AH', 34 => 'AI', 35 => 'AJ', 36 => 'AK', 37 => 'AL', 38 => 'AM', 39 => 'AN',
            40 => 'AO', 41 => 'AP', 42 => 'AQ', 43 => 'AR', 44 => 'AS', 45 => 'AT', 46 => 'AU', 47 => 'AV', 48 => 'AW', 49 => 'AX',
            50 => 'AY', 51 => 'AZ', 52 => 'BA', 53 => 'BB', 54 => 'BC', 55 => 'BD', 56 => 'BE', 57 => 'BF', 58 => 'BG', 59 => 'BH',
            60 => 'BI', 61 => 'BJ', 62 => 'BK', 63 => 'BL', 64 => 'BM', 65 => 'BN', 66 => 'BO', 67 => 'BP', 68 => 'BQ', 69 => 'BR',
            70 => 'BS', 71 => 'BT', 72 => 'BU', 73 => 'BV', 74 => 'BW', 75 => 'BX', 76 => 'BY', 77 => 'BZ', 78 => 'CA', 79 => 'CB',
            80 => 'CC', 81 => 'CD', 82 => 'CE', 83 => 'CF', 84 => 'CG', 85 => 'CH', 86 => 'CI', 87 => 'CJ', 88 => 'CK', 89 => 'CL',
            90 => 'CM', 91 => 'CN', 92 => 'CO', 93 => 'CP', 94 => 'CQ', 95 => 'CR', 96 => 'CS', 97 => 'CT', 98 => 'CU', 99 => 'CV'
        ];
        return array_slice($columns, 0, $count);
    }

    /**
     * 动态生成列名
     * @param $count
     * @return array
     */
    private static function dynamicColumn($count)
    {
        $columns = [];
        for ($i = 0; $i < $count; $i++) {
            $columns[] = self::numberToLetters($i);
        }
        return $columns;
    }


    /**
     * 根据列数获取列名
     * @param $num 第 num 列，从 0 开始的整数
     * @return string
     */
    private static function numberToLetters($num): string
    {
        $letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M','N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
        if ($num == 0) {
            return $letters[0];
        }
        $tmp = '';
        while (true) {
            $remain = $num % 26;
            $tmp = $letters[$remain] . $tmp;
            $num = intval($num / 26);
            if ($num == 0) {
                break;
            }
            $num--;
        }
        return $tmp;
    }

    /**
     * 校正Excel每行的每列数据，确保表头列数和数据个数匹配
     * @Author : Tian.Yuxing
     *
     * @param array $head
     * @param array $content
     *
     * @return array
     */
    public function correctColumn(array $head, array $content):array
    {
        $data =[];
        $cCount = count($head);
        foreach ($content as $val) {
            $line = array_values(array_slice($val, 0, $cCount));
            if (count($line) < $cCount) {
                $line = array_pad($line, $cCount, '');
            }
            $data[] = $line;
        }
        return $data;
    }

    /**
     * 判断是否使用哪种模式进行数据导出
     * @param string $savePath
     * @param array  $head
     * @param bool   $forceNormalMode
     * @param string $excelName
     * @param array  $dictDetail
     * @param string $sheetName
     * @return array
     */
    public function createExcelT(string $savePath, array $head, bool $forceNormalMode = false, string $excelName = '', array $dictDetail = [], string $sheetName = 'Sheet1'):array
    {
        //因为需要合并单元格，所以必须使用普通模式
        if (!empty($head['bigHead']) || $forceNormalMode) {
            return $this->createNormalExcel($savePath, $head, $dictDetail, $excelName, $sheetName);
        }

        //无需合并单元格，则使用内存模式
        return $this->createConstMemoryExcel($savePath, $head, $excelName, $sheetName);
    }

    /**
     * 使用内存模式初始化Excel，不支持大小标题
     * @param string $savePath
     * @param array  $head
     * @param string $excelName
     * @param string $sheetName
     * @return array
     */
    private function createConstMemoryExcel(string $savePath, array $head, string $excelName = '', string $sheetName = 'Sheet1'):array
    {
        $config = ['path' => rtrim($savePath, '/')];
        $excel  = new \Vtiful\Kernel\Excel($config);
        //设置导出的excel目标文件【固定内存模式】
        if ($excelName) {
            $fileName = str_ends_with($excelName, '.xlsx') ? $excelName : $excelName . '.xlsx';
        } else {
            $fileName = date('YmdH') . mt_rand(100, 999) . '.xlsx';
        }

        $fileObject = $excel->constMemory($fileName, $sheetName, false);

        // 设置 header
        $this->setConstMemoryHeader($fileObject, $head);

        return ['file_path' => $savePath . '/' . $fileName, 'excel_obj' => $fileObject, 'start_line' => 1];
    }

    /**
     * 内存模式设置 header
     * @param \Vtiful\Kernel\Excel $fileObject
     * @param array                $head
     * @param bool                 $isFirstSheet 是否是第一个 sheet
     * @return void
     */
    public function setConstMemoryHeader(\Vtiful\Kernel\Excel $fileObject, array $head, bool $isFirstSheet = true): void
    {
        $fileHandle = $fileObject->getHandle();

        //设置全局样式
        $fontSize = 14;
        if ($isFirstSheet) {
            $globalStyle = (new Format($fileHandle))->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)->fontSize($fontSize)->toResource();
            $fileObject->defaultFormat($globalStyle);
        }

        //计算列的字母序号
        $cCount  = count($head['smallHead']);
        $column = $cCount <= 26 ? self::staticColumn($cCount) : self::dynamicColumn($cCount);

        //设置子标题样式
        foreach ($head['smallHead'] as $i => $conf) {
            //设置单元格样式
            if (!empty($conf['bgcolor'])) {
                $style = (new Format($fileHandle))->bold()
                    ->fontSize($conf['fontSize'] ?? $fontSize)
                    ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
                    ->background($conf['bgcolor'])
                    ->border(\Vtiful\Kernel\Format::BORDER_THIN)
                    ->toResource();
            } else {
                $style = (new Format($fileHandle))->bold()
                    ->fontSize($conf['fontSize'] ?? $fontSize)
                    ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
                    ->border(\Vtiful\Kernel\Format::BORDER_THIN)
                    ->toResource();
            }

            //单元格写入文本，并设置样式
            $cname = $column[$i];
            $fileObject->insertText(0, $i, $conf['title'], null, $style)
                ->setRow("A1", 20)
                ->setColumn("{$cname}1:{$cname}1", $conf['width'] ?? 30);
        }
    }

    /**
     * 使用普通模式初始化Excel，支持大小标题
     * @param string $savePath
     * @param array  $head
     * @param array  $dictDetail
     * @param string $excelName
     * @param string $sheetName
     * @return array
     */
    private function createNormalExcel(string $savePath, array $head, array $dictDetail = [], string $excelName = '', string $sheetName = 'Sheet1'): array
    {
        $config = ['path' => rtrim($savePath, '/')];

        if ($excelName) {
            $fileName = str_ends_with($excelName, '.xlsx') ? $excelName : $excelName . '.xlsx';
        } else {
            $fileName = date('YmdH') . mt_rand(100, 999) . '.xlsx';
        }

        //设置导出的excel目标文件【普通模式】
        $excel  = new \Vtiful\Kernel\Excel($config);
        $fileObject = $excel->fileName($fileName, $sheetName);

        //组装下拉选项数据
        if (!empty($dictDetail)) {
            $validation = new \Vtiful\Kernel\Validation();
            foreach ($dictDetail as $value) {
                if (!empty($value['location'])) {
                    $validation->validationType(\Vtiful\Kernel\Validation::TYPE_LIST)->valueList($value['ptions']);
                    $fileObject->validation($value['location'], $validation->toResource());
                }
            }
        }

        $fileHandle = $fileObject->getHandle();

        //设置全局样式
        $globalStyle = (new Format($fileHandle))->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)->fontSize(14)
            ->border(\Vtiful\Kernel\Format::BORDER_THIN)->toResource();
        $excel->defaultFormat($globalStyle);

        //计算列的字母序号
        $cCount  = count($head['smallHead']);
        $column = $cCount <= 26 ? self::staticColumn($cCount) : self::dynamicColumn($cCount);

        //设置大标题样式
        $lineNum = 1;

        if (!empty($head['headTips'])) {
            // https://xlswriter-docs.viest.me/zh-cn/yang-shi-lie-biao/font-rich
            foreach ($head['headTips'] as $tip) {
                // 先创建单元格样式，设置对齐方式
                $cellFormat = new Format($fileHandle);
                $cellFormat->align(Format::FORMAT_ALIGN_LEFT, Format::FORMAT_ALIGN_VERTICAL_CENTER);

                //合并单元格
                if (!empty($tip['range'])) {
                    $fileObject->mergeCells($tip['range'], '');
                }

                $richText = [];
                foreach ($tip['list'] as $item) {
                    $format  = new Format($fileHandle);
                    $format->fontSize($item['fontSize'])->fontColor($item['color'] ?? Format::COLOR_BLACK);
                    $format->align(Format::FORMAT_ALIGN_LEFT);
                    if (isset($item['underline']) && $item['underline'] === true) {
                        $format->underline(Format::UNDERLINE_SINGLE);
                    }
                    if (isset($item['bold']) && $item['bold'] === true) {
                        $format->bold();
                    }
                    if (!empty($item['font'])) {
                        $format->font($item['font']);
                    }

                    $richText[] = new \Vtiful\Kernel\RichString($item['title'], $format->toResource());
                }

                $fileObject->insertRichText($tip['row'], $tip['column'], $richText, $cellFormat->toResource());

                //行高
                if (!empty($tip['height'])) {
                    $excel->setRow($lineNum, $tip['height']);
                }

                $lineNum++;
            }
        }

        if ($head['bigHead']) {
            $maxFontSize = 0;
            foreach ($head['bigHead'] as $bval) {
                $fontSize = $bval['fontSize'] ?? 19;
                $maxFontSize = $maxFontSize>$fontSize ? $maxFontSize : $fontSize;

                //设置单元格样式
                $style = (new Format($fileHandle))->bold()
                    ->border(\Vtiful\Kernel\Format::BORDER_THIN)
                    ->fontSize($fontSize)
                    ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
                    ->background($bval['bgcolor'])
                    ->toResource();

                //单元格写入文本，并设置样式
                $fileObject->mergeCells($bval['range'], $bval['title'], $style);
            }
            // 设置第一行的高度为 50
            $height = intval($maxFontSize * 1.7);
            $excel->setRow($lineNum, $height);
            $lineNum++;
        }

        //设置子标题样式
        $maxFontSize = 0;
        foreach ($head['smallHead'] as $i => $conf){
            $fontSize = $conf['fontSize'] ?? 14;
            empty($conf['bgcolor']) && $conf['bgcolor'] = Format::COLOR_WHITE;
            $maxFontSize = $maxFontSize>$fontSize ? $maxFontSize : $fontSize;

            //设置单元格样式
            $style = (new Format($fileHandle))->bold()
                ->fontSize($fontSize)
                ->background($conf['bgcolor'])
                ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
                ->border(\Vtiful\Kernel\Format::BORDER_THIN)
                ->toResource();

            //单元格写入文本，并设置样式
            $cname = $column[$i];
            $row = $lineNum - 1;
            $fileObject->insertText($row, $i, $conf['title'], null, $style)
                ->setRow("A{$lineNum}", 20)
                ->setColumn("{$cname}{$lineNum}:{$cname}{$lineNum}", $conf['width'] ?? 30);
        }

        $height = intval($maxFontSize * 1.7);
        $excel->setRow($lineNum++, $height);

        if (!empty($head['smallHeadTps'])) {
            foreach ($head['smallHeadTps'] as $item) {
                $fontSize = $item['fontSize'] ?? 14;
                $format = new Format($fileHandle);
                $format->fontSize($fontSize)->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER);

                if (!empty($item['color'])) {
                    $format->fontColor($item['color']);
                }

                if (!empty($item['bgcolor'])) {
                    $format->background($item['bgcolor']);
                }

                $style = $format->toResource();
                $fileObject->mergeCells($item['range'], $item['title'], $style);
            }

            $height = intval($maxFontSize * 3);
            $excel->setRow($lineNum + 2, $height);
        }

        $startLine = count(array_filter($head));

        $fileObject->freezePanes($startLine, 0);
        return ['file_path'=>$savePath . '/' . $fileName, 'excel_obj' => $fileObject, 'start_line' => $startLine];
    }

    /**
     * 添加工作表
     * @Author : Tian.Yuxing
     *
     * @param        $excelObj
     * @param array  $head
     * @param string $sheet
     *
     * @return mixed
     */
    private function setSheet($excelObj, array $head, string $sheet)
    {
        //检查工作表是否存在
        if (false != $excelObj->existSheet($sheet)){
            return $excelObj->checkoutSheet($sheet);
        }
        //添加工作表
        $title = array_column($head,'title');
        $excelObj->addSheet($sheet);
        return $excelObj->header($title)->freezePanes(1, 0);
    }
    /**
     * 将数据写入Excel
     * @Author : Tian.Yuxing
     *
     * @param  $excelObj
     * @param array  $head
     * @param array  $data
     *
     * @return mixed
     */
    private function writeExcel($excelObj, array $head, array $data)
    {
        //校正Excel每行的每列数据，确保表头列数和数据个数匹配
        $data  = $this->correctColumn($head, $data);

        //将本次查询到的数据一次性写入到excel中
        return  $excelObj->data($data);
    }

    /**
     * 设置excel数据
     * @Author : Tian.Yuxing
     *
     * @param       $excelObj
     * @param int   $startLine
     * @param array $data
     * @param array $columnStyle
     * @return int
     */
    public function setExcelDataT($excelObj,int $startLine, array $data, array $columnStyle = [])
    {
        $fileHandle = $excelObj->getHandle();

        foreach ($data as $rowData){
            $mergeCells = [];
            if (isset($rowData['mergeCells'])) {
                $mergeCells = $rowData['mergeCells'];
                unset($rowData['mergeCells']);
            }

            if (!empty($columnStyle)) {
                $columnNum = 0;
                foreach ($rowData as $field => $value) {
                    if (is_array($value)) {
                        continue;
                    }

                    $style = null;
                    if (isset($columnStyle[$field])) {
                        $format = new Format($fileHandle);
                        if (!empty($columnStyle[$field]['fontSize'])) {
                            $format->fontSize($columnStyle[$field]['fontSize']);
                        }
                        if (!empty($columnStyle[$field]['bgcolor'])) {
                            $format->background($columnStyle[$field]['bgcolor']);
                        }

                        if (!empty($columnStyle[$field]['align'])) {
                            $format->align(...$columnStyle[$field]['align']);
                        }

                        if (!empty($columnStyle[$field]['border'])) {
                            $format->border($columnStyle[$field]['border']);
                        }

                        $style = $format->toResource();
                    }

                    $excelObj->insertText($startLine, $columnNum++, $value, null, $style);
                }
            } else {
                //将本次查询到的数据一次性写入到excel中
                $excelObj->setCurrentLine($startLine)->data([array_values($rowData)]);
            }

            //判断是否需要合并单元格  有列样式不能合并单元格，否则样式会出问题
            if ($mergeCells && empty($columnStyle)) {
                foreach ($mergeCells as $column => $range) {
                    $excelObj->mergeCells($range, $rowData[$column]);
                }
            }
            $startLine++;
        }
        return $startLine;
    }
    /**
     * 输入保存Excel
     * @Author : Tian.Yuxing
     *
     * @param string $excelObj
     *
     * @return bool
     */
    private function outputExcel($excelObj)
    {
        $excelObj->output();
        return true;
    }

    /**
     * 数据格式化
     * @Author : Tian.Yuxing
     *
     * @param int   $startLine
     * @param array $headColumn
     * @param array $sourceData
     *
     * @return array
     */
    private function dataFactory(int &$startLine, array $headColumn, array $sourceData):array
    {
        //获取表头的大字段名称
        $fieldDict = [];
        foreach ($headColumn as $field) {
            if (strpos($field, '.')) {
                list($field, $smk) = explode('.', $field);
                $fieldDict[$field][$smk] = 1;
            } else {
                $fieldDict[$field] = 1;
            }
        }

        //获取单元数组中哪个子数组的长度最长，然后根据这个长度来遍历并取出每个子数组的值
        $maxLength = 1;
        foreach ($sourceData as $key => $value) {
            if (is_array($value) && isset($fieldDict[$key]) && count($value) > $maxLength) {
                $maxLength = count($value);
            }
        }

        //根据最长的子数组长度进行遍历
        $result = [];
        for ($i = 1; $i <= $maxLength; $i++) {
            $initData = array_fill_keys($headColumn, '');//数据初始化
            $num = ($startLine + $i-1);//设置行号
            //遍历改组数据
            foreach ($sourceData as $bf => $value) {
                if (!isset($fieldDict[$bf])) {
                    continue;
                }
                //获取数组中的值
                if (is_array($value) && !empty($value[$i-1])) {
                    foreach ($value[$i-1] as $sk => $sv) {
                        if (isset($fieldDict[$bf][$sk])) {
                            $bsk = $bf . '.' . $sk;
                            $initData[$bsk] = $sv;
                        }
                    }
                }

                //只有末次循环时进行赋值，其他不进行赋值，便于后面合并单元格
                if ($i == $maxLength && !is_array($value)) {
                    $initData[$bf] = $value;
                    $index = array_search($bf, $headColumn);
                    $cell = $this->numberToLetters($index);
                    $initData["mergeCells"][$bf] = "{$cell}$startLine:$cell{$num}";
                }
            }
            $result[$i] = $initData;
        }
        $startLine = ($num+1);
        return $result;
    }

    /**
     * 格式化返回信息
     * @Author : Tian.Yuxing
     *
     * @param array $pageData
     * @param array $excelHead
     * @param null  $startLine
     * @return array
     */
    private function formatDataT(array $pageData, array $excelHead = [], $startLine = null): array
    {
        $head = $excelHead;
        $lineCount = count(array_filter($head));

        //获取excel的表头排序字段
        $fieldArr = array_column($head['smallHead'], 'field');
        $lineData = [];
        $startLine = is_null($startLine) ? $lineCount+1 : $startLine + 1;
        foreach ($pageData as $key => $group) {
            $groupData = $this->dataFactory($startLine, $fieldArr, $group);
            $lineData = array_merge($lineData, $groupData);
        }

        //如果指定了(field)字段顺序，则提取本次查询结果中的有效字段，进行排序
        $result = [];
        $fieldDict = array_column($head['smallHead'], 'title', 'field');
        foreach ( $lineData as $key => $item ) {
            //按照$fieldDict的顺序进行排序
            $intersect = array_intersect_key($item, $fieldDict);

            //获取$intersect数组中不在$item数组中的键
            $diff = array_diff_key($item, $intersect);
            $result[] = array_merge($intersect, $diff);
        }

        return $result;
    }
}